package flinkSql.sink;

import bean.SensorReading;
import org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.streaming.api.datastream.SingleOutputStreamOperator;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.junit.Test;

import static org.apache.flink.table.api.Expressions.$;

public class Flink_Sink_MySQL {

    @Test
    public void test() throws Exception {
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment().setParallelism(1);
        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);

        SingleOutputStreamOperator<SensorReading> input = env.socketTextStream("node193", 9999)
                .map((MapFunction<String, SensorReading>) value -> {
                    String[] split = value.split(",");
                    return new SensorReading(split[0], Long.parseLong(split[1]), Double.parseDouble(split[2]));
                });

        //对流注册一个表
        Table table = tableEnvironment.fromDataStream(input);

        //table API DSL风格
        Table tableResult = table.groupBy($("id")).select($("id"),$("temp").sum());

        //SQL风格
        tableEnvironment.createTemporaryView("sensor",input);
        Table sqlResult = tableEnvironment.sqlQuery("select id,sum(temp) from sensor group by id");

        //创建MySQL
        String sinkDDL = "create table jdbcOutputTable (" +
                " id varchar(20) not null, " +
                " temp double not null " +
                ") with (" +
                " 'connector.type' = 'jdbc', " +
                " 'connector.url' = 'jdbc:mysql://node193:6603/xmanager', " +
                " 'connector.table' = 'testdh', " +
                " 'connector.driver' = 'com.mysql.jdbc.Driver', " +
                " 'connector.username' = 'xmanager', " +
                "  'connector.write.flush.max-rows' = '1', " +
                " 'connector.password' = 'Xmanager_666.cn' )";
        //变成连接器
        tableEnvironment.executeSql(sinkDDL);

        //将数据写入文件系统
        tableResult.executeInsert("jdbcOutputTable");
        sqlResult.executeInsert("jdbcOutputTable");

        env.execute();
    }
}
